package br.edu.ufc.deti.doodlematricula.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.StringTokenizer;

import com.javaunderground.jdbc.DebugLevel;
import com.javaunderground.jdbc.StatementFactory;

import br.edu.ufc.deti.doodlematricula.beans.DiaDisciplina;
import br.edu.ufc.deti.doodlematricula.beans.Horario;
import br.edu.ufc.deti.doodlematricula.beans.SolicitacoesDisciplina;
import br.edu.ufc.deti.doodlematricula.beans.Usuario;

public class PrsDisciplina {
    private static PrsDisciplina instancia = null;
    
    private PrsDisciplina() {}

    public static PrsDisciplina getInstancia() {
        if (instancia == null) {
            instancia = new PrsDisciplina();
        }
        return instancia;
    }

	public ArrayList<SolicitacoesDisciplina> getSolicitacoesDisciplina(
			Connection conn, String idUsuario, String idDisciplina) throws Exception {
		
        String select = "";
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        SolicitacoesDisciplina beanSolicitacao = null;
        LinkedHashMap<Usuario, ArrayList<DiaDisciplina>> solicitacaoUsuario = null;
        ArrayList<SolicitacoesDisciplina> solicitacoes = new ArrayList<SolicitacoesDisciplina>();

        try {
        	select = "select distinct u.id, u.nome "+
				  	 "  from usuario u, dia_disciplina dd, "+
				     "       horarios_disciplina hd, solicitacao_disciplina sd "+
					 " where sd.idDisciplina = ? "+
					 "   and sd.idUsuario = u.id "+
					 "   and sd.idDiaDisciplina = dd.id "+
					 "   and sd.idHorario = hd.id ";
        	pstmt = StatementFactory.getStatement(conn,select,DebugLevel.OFF);
        	pstmt.setString(1,idDisciplina);
        	rs = pstmt.executeQuery();
      	
        	Usuario usuario = null;
        	ArrayList<DiaDisciplina> horariosUsuario = null;
        	
        	while (rs.next()) {
        		usuario = new Usuario();
            	usuario.setIdUsuario(rs.getString("id"));
            	usuario.setNome(rs.getString("nome"));
            	
            	horariosUsuario = getSolicitacaoUsuario(conn, idDisciplina, usuario.getIdUsuario());
            	
            	solicitacaoUsuario = new LinkedHashMap<Usuario, ArrayList<DiaDisciplina>>();
            	solicitacaoUsuario.put(usuario, horariosUsuario);
            	
            	beanSolicitacao = new SolicitacoesDisciplina();
            	beanSolicitacao.setIdDisciplina(idDisciplina);
            	beanSolicitacao.setSolicitacao(solicitacaoUsuario);
            	
            	solicitacoes.add(beanSolicitacao);
            	
            	usuario = null;
            	horariosUsuario = null;
            	solicitacaoUsuario = null;
            	beanSolicitacao = null;
            }
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return solicitacoes;
	}
	public ArrayList<DiaDisciplina> getSolicitacaoUsuario(
			Connection conn, String idDisciplina, String idUsuario) throws Exception {
        String select = "";
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<DiaDisciplina> horarios = new ArrayList<DiaDisciplina>();

        try {
        	select = "select dd.id as codDia, dd.dscDia, hd.id as codHorario, hd.dscHorario "+
					 "  from usuario u, dia_disciplina dd, "+
					 "      horarios_disciplina hd, solicitacao_disciplina sd "+
					 "where sd.idDisciplina = ? "+
					 "  and sd.idUsuario = ? "+
					 "  and sd.idUsuario = u.id "+
					 "  and sd.idDiaDisciplina = dd.id "+
					 "  and sd.idHorario = hd.id";
        	pstmt = StatementFactory.getStatement(conn,select,DebugLevel.OFF);
        	pstmt.setString(1,idDisciplina);
        	pstmt.setString(2,idUsuario);
        	rs = pstmt.executeQuery();
        	
        	DiaDisciplina dia = null;
        	Horario horario = null;
        	while (rs.next()) {
            	dia = new DiaDisciplina();
            	dia.setCodDiaDisciplina(rs.getString("codDia"));
            	dia.setDscDiaDisciplina(rs.getString("dscDia"));
            	
            	horario = new Horario();
            	horario.setCodHorario(rs.getString("codHorario"));
            	horario.setDscHorario(rs.getString("dscHorario"));
            	
            	dia.setHorario(horario);
            	
            	horarios.add(dia);
            	
            	dia = null;
            	horario = null;
          }
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return horarios;	
	}

	public ArrayList<Horario> getSolicitacoesDisciplina(Connection conn) throws Exception {
        String select = "";
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<Horario> horarios = new ArrayList<Horario>();

        try {
        	select = "SELECT id as codHorario, dscHorario FROM horarios_disciplina";
        	pstmt = StatementFactory.getStatement(conn,select,DebugLevel.OFF);
        	rs = pstmt.executeQuery();
        	
        	Horario horario = null;
        	while (rs.next()) {
            	horario = new Horario();
            	horario.setCodHorario(rs.getString("codHorario"));
            	horario.setDscHorario(rs.getString("dscHorario"));
            	horarios.add(horario);
            	horario = null;
        	}
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return horarios;	
	}

	public LinkedHashMap<String, LinkedHashSet<Usuario>> getSolicitacoesDisciplina(Connection conn, String idDisciplina) throws Exception {
        String select = "";
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList<DiaDisciplina> dias = null;
        LinkedHashSet<Usuario> usuarios = null;
        LinkedHashMap<String, LinkedHashSet<Usuario>> horarios = new LinkedHashMap<String, LinkedHashSet<Usuario>>();

        try {
        	String[] combinacoesDias = {//"1","2","3","4","5",
        								//"1,2",
        								"1,3",
        								//"1,4","1,5","2,3",
        								"2,4"
        								//"2,5","3,4","3,5","4,5" 
        								//"1,2,3","1,2,4","1,2,5","1,3,4","1,3,5","1,4,5",
        								//"1,2,3,4","1,2,3,5","2,3,4,5",
        								//"1,2,3,4,5"
        								};
        	String[] combinacoesHorarios = {"1", "2", "3", "4", "5", "6", "7"};
        	String dias_string = "", horario_string = "";
    		for (int i = 0; i < combinacoesDias.length; i++) {
    			dias = new ArrayList<DiaDisciplina>();
    			dias_string = ""; horario_string = ""; 
				String diaDisciplina = combinacoesDias[i];
				for (int j = 0; j < combinacoesHorarios.length; j++) {
	    			dias = new ArrayList<DiaDisciplina>();
	    			dias_string = ""; horario_string = ""; 

	    			String horariosDisciplina = combinacoesHorarios[j];
				   	select = "select "+
								"distinct dd.id as codDia, dd.dscDia, "+ 
								 "        hd.id as codHorario, hd.dscHorario "+
								 "from usuario u, dia_disciplina dd, "+
								 "      horarios_disciplina hd, solicitacao_disciplina sd "+
								 "where sd.idDisciplina = ? "+
								 "  and sd.idDiaDisciplina in ( "+diaDisciplina+" ) "+
								 "  and sd.idHorario = ? "+
								 "  and sd.idUsuario = u.id "+
								 "  and sd.idDiaDisciplina = dd.id "+
								 "  and sd.idHorario = hd.id";
		        	
		        	pstmt = StatementFactory.getStatement(conn,select,DebugLevel.OFF);
		        	pstmt.setString(1, idDisciplina);
		        	//pstmt.setString(2, diaDisciplina);
		        	pstmt.setString(2, horariosDisciplina);
		        	rs = pstmt.executeQuery();

		        	DiaDisciplina dia = null;
		        	Horario horario = null;
		        	while (rs.next()) {
		            	dia = new DiaDisciplina();
		            	dia.setCodDiaDisciplina(rs.getString("codDia"));
		            	dia.setDscDiaDisciplina(rs.getString("dscDia"));
		            	
		            	horario = new Horario();
		            	horario.setCodHorario(rs.getString("codHorario"));
		            	horario.setDscHorario(rs.getString("dscHorario"));
		            	
		            	dia.setHorario(horario);
		            	
		            	dias.add(dia);
		            	
		            	dia = null;
		            	horario = null;
		        	}

	            	usuarios = getUsuariosDisciplinaDiaHorario(conn, idDisciplina, diaDisciplina, horariosDisciplina);
	            	if ( usuarios.size() > 0 ){
	            		dias_string = "";
	            		for (DiaDisciplina diaDisciplina2 : dias) {
	            			horario_string = diaDisciplina2.getHorario().getDscHorario();
							dias_string += diaDisciplina2.getDscDiaDisciplina()+" / ";
						}
	            		dias_string += horario_string; 
	            		horarios.put(dias_string, usuarios);
	            	}
					dias = null;
					dias_string = null;
				}
				dias = null;
				dias_string = null;
			}
    		
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return horarios;
	}
	private ArrayList<String> stringToArray( String stringWithDelimitador ) {
		StringTokenizer st = new StringTokenizer(stringWithDelimitador, ",", false);
		ArrayList<String> al = new ArrayList<String>();
		al.add(st.nextToken());
		while (st.hasMoreTokens()) {
			al.add(st.nextToken());
		}
		return al;
	}

	
	private LinkedHashSet<Usuario> getUsuariosDisciplinaDiaHorario( 
			Connection conn, String idDisciplina, String diasDisciplina, String codHorario) throws Exception {
		
        String select = "", select2 = "";
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        LinkedHashSet<Usuario> usuarios = new LinkedHashSet<Usuario>();
        ArrayList<String> codsDiasDisciplina = stringToArray(diasDisciplina);
        try {
        	String usuarios_lista = "";
        	boolean conjuntoDeDiasRealmenteOpinado = false;
        	for (String dia : codsDiasDisciplina) {
/*            	select = "select u.id, u.nome, u.matricula "+
						 "  from usuario u, dia_disciplina dd, "+
						 "       horarios_disciplina hd, solicitacao_disciplina sd "+
						 " where sd.idDisciplina = ? "+
						 "   and sd.idDiaDisciplina = ? "+ 
						 "   and sd.idHorario = ? "+
						 "   and sd.idUsuario = u.id "+
						 "   and sd.idDiaDisciplina = dd.id "+
						 "   and sd.idHorario = hd.id ";
*/
            	select = "select u.id, dd.id as codDia, dd.dscDia, "+
				         "       hd.id as codHorario, hd.dscHorario "+
				         "  from usuario u, dia_disciplina dd, "+
				         "       horarios_disciplina hd, solicitacao_disciplina sd "+
				         " where sd.idDisciplina = ? "+
				         "   and sd.idDiaDisciplina = ? "+
				         "   and sd.idHorario = ? "+
				         "   and sd.idUsuario = u.id "+
				         "   and sd.idDiaDisciplina = dd.id "+
				         "   and sd.idHorario = hd.id";
            
            	pstmt = StatementFactory.getStatement(conn,select,DebugLevel.OFF);
			   	pstmt.setString(1,idDisciplina);
			   	pstmt.setString(2,dia);
			   	pstmt.setString(3,codHorario);
			   	rs = pstmt.executeQuery();
			   	int contador = 0;
			   	while (rs.next()) {
			   		usuarios_lista += rs.getString("id")+",";
			   		contador = contador+1;
				}
			   	if(contador>0){
			   		conjuntoDeDiasRealmenteOpinado = true;
			   	}else{conjuntoDeDiasRealmenteOpinado = false;}
			}
        	if (conjuntoDeDiasRealmenteOpinado) {
            	select2 = "select distinct u.id, u.nome, u.matricula "+
						 "  from usuario u, dia_disciplina dd, "+
						 "       horarios_disciplina hd, solicitacao_disciplina sd "+
						 " where sd.idDisciplina = ? "+
						 "   and sd.idDiaDisciplina in ("+diasDisciplina+") "+
						 "   and sd.idUsuario in ("+usuarios_lista.substring(0, usuarios_lista.length()-1)+") "+
						 "   and sd.idHorario = ? "+
						 "   and sd.idUsuario = u.id "+
						 "   and sd.idDiaDisciplina = dd.id "+
						 "   and sd.idHorario = hd.id ";
				   	pstmt = StatementFactory.getStatement(conn, select2,DebugLevel.OFF);
				pstmt.setString(1, idDisciplina);
				//pstmt.setString(2, diasDisciplina);
				//pstmt.setString(3, usuarios_lista.substring(0, usuarios_lista.length()-1));
				pstmt.setString(2, codHorario);
				rs = pstmt.executeQuery();
				Usuario usuario = null;
				while (rs.next()) {
					usuario = new Usuario();
					usuario.setIdUsuario(rs.getString("id"));
					usuario.setNome(rs.getString("nome"));
					usuario.setMatricula(rs.getString("matricula"));
					usuarios.add(usuario);
					usuario = null;
				}
			}
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return usuarios;
	}

	public LinkedHashMap<String, LinkedHashSet<Usuario>> setSolicitacoesDisciplina( 
			Connection conn, String idUsuario, String idDisciplina, ArrayList<DiaDisciplina> solicitacao) throws Exception {

        String query = "";
        ResultSet rs = null;
        boolean retorno = false;
        PreparedStatement pstmt = null;
        LinkedHashMap<String, LinkedHashSet<Usuario>> a = null;

        try {
        	if (deleteSolicitacoesUsuario(conn, idUsuario, idDisciplina)){
	        	for (DiaDisciplina diaDisciplina : solicitacao) {
	            	query = "INSERT INTO solicitacao_disciplina " +
	            			"(idDisciplina, idUsuario, idDiaDisciplina, idHorario) " +
	            			"VALUES (?, ?, ?, ?)";
	            	pstmt = StatementFactory.getStatement(conn,query,DebugLevel.OFF);
	        		pstmt.setString(1, idDisciplina);
	        		pstmt.setString(2, idUsuario);
	        		pstmt.setString(3, diaDisciplina.getCodDiaDisciplina());
	        		pstmt.setString(4, diaDisciplina.getHorario().getCodHorario());
	            	int rowsIserted = pstmt.executeUpdate();
	        		if(rowsIserted > 0)
	        			retorno = true;
				}
        	}
        	if (retorno){
        		a = getSolicitacoesDisciplina(conn, idDisciplina);
        	}
        }catch(Exception e){
        	throw e;
        }finally{
        	if(rs != null) rs.close();
        	if(pstmt != null) pstmt.close();
        }    
		return a;
	}
	

	private boolean deleteSolicitacoesUsuario(Connection conn, String idUsuario, String idDisciplina) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
	    boolean retorno = false;
	    
		StringBuffer sql1 = new StringBuffer();
		sql1.append("select id from solicitacao_disciplina where idDisciplina = ? and idUsuario = ?");
		
		StringBuffer sql2 = new StringBuffer();
		sql2.append("delete from solicitacao_disciplina where idDisciplina = ? and idUsuario = ?");
		
	    boolean usuarioJaSolicitou = false;
		try {
			ps = StatementFactory.getStatement(conn, sql1.toString(), DebugLevel.OFF);
			ps.setString(1, idDisciplina);
			ps.setString(2, idUsuario);

			rs = ps.executeQuery();
        	while (rs.next()) {
        		usuarioJaSolicitou = Integer.parseInt(rs.getString("id"))>0?true:false;
        	}	

        	if(usuarioJaSolicitou){
				ps = StatementFactory.getStatement(conn, sql2.toString(), DebugLevel.OFF);
				ps.setString(1, idDisciplina);
				ps.setString(2, idUsuario);
	
				int rowsIserted = ps.executeUpdate();
				if(rowsIserted > 0)
					retorno = true;		
        	}else{retorno = true;}
		} catch (Exception e) {
			throw e;
		}
		return retorno;
		
	}
}
